import java.sql.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;

/**
 * 本类的writeReport根据本地数据库的数据撰写统计报告</p>
 * 在实际使用过程中，应当
 * 1.先调用本类的getRawData方法；
 * 2.人工插入、删除某些项目；
 * 3.使用GetStatisticsData类进行数据分析；
 * 4.使用WriteReport类撰写统计月报。★
 */
public class WriteReport {
    private static final String[] CHINA_NUMS = {"一", "二", "三", "四", "五", "六", "七", "八", "九", "十", "十一", "十二"};
    public static final NumberFormat DEC_FORMAT = new DecimalFormat("#.0");
    public static final NumberFormat INT_FORMAT = new DecimalFormat("#,###");

    public static void main(String[] args) {
        writeReport(2022, 1);
    }

    public static void writeReport(int year, int month) {
        String currentMonth = GetRawData.monthToString(year, month);
        String nextMonth;
        String previousMonth;
        String lastYearMonth = GetRawData.monthToString(year - 1, month);
        if (month > 1) {
            previousMonth = GetRawData.monthToString(year, month - 1);
        } else {
            previousMonth = GetRawData.monthToString(year - 1, 12);
        }

        if (month < 12) {
            nextMonth = GetRawData.monthToString(year, month + 1);
        } else {
            nextMonth = GetRawData.monthToString(year + 1, 1);
        }

        StringBuilder report = new StringBuilder();
        try {
            int totalCountVSV;
            int totalSumVSV;

            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection localConnection = DriverManager.
                    getConnection(LocalAccount.URL, LocalAccount.USER, LocalAccount.PASSWORD);
            {
                /* 前言 */
                report.append("【").append(CHINA_NUMS[month - 1]).append("月】中文VOCALOID与Synthesizer V统计月报\n\n");
                report.append("本期收录时间").append(currentMonth).append("-01 4:00 ~ ").append(nextMonth).append("-01 4:00\n");
                report.append("注意：本统计月报并非官方统计月报\n");
                report.append("== 上期回顾 ==\n\n\n");
            }
            System.out.println("前言√");
            {
                /* 总体数据 */
                report.append("== 总体情况 ==\n");
                int[] values = new int[3]; // 本期 上月 去年

                String[] months = {currentMonth, previousMonth, lastYearMonth};
                {
                    String sql = "SELECT `count` FROM table_statistics WHERE `month`=? AND `type` = '总计';";
                    for (int i = 0; i < 3; i++) {
                        PreparedStatement preparedStatement = localConnection.prepareStatement(sql);
                        preparedStatement.setString(1, months[i]);
                        preparedStatement.executeQuery();
                        ResultSet resultSet = preparedStatement.getResultSet();
                        while (resultSet.next()) {
                            values[i] = resultSet.getInt("count");
                        }
                    }
                    report.append("本期中文VOCALOID与Synthesizer V共")
                            .append(INT_FORMAT.format(values[0])).append("个投稿")
                            .append("（同比").append(compare(values[0], values[2]))
                            .append("，环比").append(compare(values[0], values[1]))
                            .append("）. \n");
                    totalCountVSV = values[0];
                }
                {
                    String sql = "SELECT `over1000` FROM table_statistics WHERE `month`=? AND `type` = '总计';";
                    for (int i = 0; i < 3; i++) {
                        PreparedStatement preparedStatement = localConnection.prepareStatement(sql);
                        preparedStatement.setString(1, months[i]);
                        preparedStatement.executeQuery();
                        ResultSet resultSet = preparedStatement.getResultSet();
                        while (resultSet.next()) {
                            values[i] = resultSet.getInt("over1000");
                        }
                    }
                    report.append("本期中文VOCALOID与Synthesizer V新曲中播放量超过1000的")
                            .append(INT_FORMAT.format(values[0])).append("个")
                            .append("（同比").append(compare(values[0], values[2]))
                            .append("，环比").append(compare(values[0], values[1]))
                            .append("）. \n");
                }
                {
                    String sql = "SELECT `sum` FROM table_statistics WHERE `month`=? AND `type` = ?;";
                    getDateFromDB(localConnection, values, months, "总计", sql, "sum");

                    report.append("本期中文VOCALOID与Synthesizer V新曲播放量")
                            .append(INT_FORMAT.format(values[0] / 10000)).append("万")
                            .append("（同比").append(compare(values[0], values[2]))
                            .append("，环比").append(compare(values[0], values[1]))
                            .append("）. \n\n");
                    totalSumVSV = values[0];
                }
            }
            System.out.println("总体√");

            {
                /* 殿堂曲与传说曲 */
                report.append("== 殿堂曲与传说曲 ==\n");
                report.append("本期新曲中，播放量达到10万的有：\n");
                report.append(GetRawData.getCurrentDiantang(year, month));
                report.append("\n\n");
            }
            System.out.println("殿堂曲与传说曲√");

            {
                /* 引擎 */
                int[] values = new int[3]; // 本期 上月 去年
                String[] months = {currentMonth, previousMonth, lastYearMonth};
                String[] types = {"VOCALOID", "Synthesizer V", "Vsinger", "五维介质",
                        "洛天依", "言和", "乐正绫", "星尘", "赤羽"};
                for (int j = 0; j < types.length; j++) {
                    if (j == 0) {
                        report.append("== 引擎 ==\n");
                    }
                    if (j == 2) {
                        report.append("\n== 团组 ==\n");
                    }
                    if (j == 4) {
                        report.append("\n== 歌姬 ==\n");
                        report.append("（部分歌姬）\n");
                    }

                    report.append("=== ").append(types[j]).append(" ===\n");

                    {
                        String sql = "SELECT `count` FROM table_statistics WHERE `month`=? AND `type`=?;";
                        getDateFromDB(localConnection, values, months, types[j], sql, "count");
                        report.append("本期").append(types[j]).append("共")
                                .append(INT_FORMAT.format(values[0])).append("个投稿")
                                .append("（同比").append(compare(values[0], values[2]))
                                .append("，环比").append(compare(values[0], values[1]));
                        if (j < 2) {
                            report.append("）. 占中文VOCALOID&Synthesizer比例")
                                    .append(DEC_FORMAT.format(values[0] * 100.0 / totalCountVSV))
                                    .append("% .\n");
                        } else {
                            report.append("）.\n");
                        }

                    }
                    {
                        String sql = "SELECT `sum` FROM table_statistics WHERE `month`=? AND `type`=?;";
                        getDateFromDB(localConnection, values, months, types[j], sql, "sum");
                        report.append("本期").append(types[j]).append("共")
                                .append(INT_FORMAT.format(values[0] / 10000)).append("万播放")
                                .append("（同比").append(compare(values[0], values[2]))
                                .append("，环比").append(compare(values[0], values[1]));
                        if (j < 2) {
                            report.append("）. 占中文VOCALOID&Synthesizer比例")
                                    .append(DEC_FORMAT.format(values[0] * 100.0 / totalSumVSV))
                                    .append("% .\n");
                        } else {
                            report.append("）.\n");
                        }

                    }
                }
                report.append("\n");
            }
            System.out.println("引擎与团组√");

            {
                report.append("== UP主 ==\n")
                        .append("=== UP主播放量 ===\n")
                        .append("本段统计的是各UP主在本期内，所有VOCALOID·UTAU区投稿的总播放量增长情况。（仅保留前10）\n");
                GetRawData.getDeltaViewOfMembers(report, year, month);
                report.append("\n");
            }
            System.out.println("UP主播放增长情况√");

            {
                report.append("=== 涨粉 ===\n")
                        .append("本段统计的是部分UP主在本期内的粉丝数增长情况。\n");
                GetRawData.getDeltaFansOfMembers(report, year, month);
                report.append("\n\n");
            }
            System.out.println("UP主涨粉情况√");

        } catch (SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }

        System.out.println(report.toString());
    }

    /**
     * 从本地数据库获取相应的数据
     *
     * @param localConnection 连接
     * @param values          保存值
     * @param months          月份
     * @param type            类型
     * @param sql             查询语句
     * @throws SQLException 可能会抛出SQLException
     */
    private static void getDateFromDB(Connection localConnection, int[] values, String[] months, String type, String sql, String filedName) throws SQLException {
        if (type.equals("星尘")) {
            values[0] = 0;
            values[1] = 0;
            values[2] = 0;
            String[] tys = {"星尘", "星尘Minus"};
            for (String ty : tys)
                for (int i = 0; i < 3; i++) {
                    PreparedStatement preparedStatement = localConnection.prepareStatement(sql);
                    preparedStatement.setString(1, months[i]);
                    preparedStatement.setString(2, ty);
                    preparedStatement.executeQuery();
                    ResultSet resultSet = preparedStatement.getResultSet();
                    while (resultSet.next()) {
                        values[i] += resultSet.getInt(filedName);
                    }
                }
        } else {
            for (int i = 0; i < 3; i++) {
                PreparedStatement preparedStatement = localConnection.prepareStatement(sql);
                preparedStatement.setString(1, months[i]);
                preparedStatement.setString(2, type);
                preparedStatement.executeQuery();
                ResultSet resultSet = preparedStatement.getResultSet();
                while (resultSet.next()) {
                    values[i] = resultSet.getInt(filedName);
                }
            }
        }
    }

    /**
     * 比较两个值，返回诸如“上升25.0%”形式的字符串
     *
     * @param cur  当前值
     * @param base 基期值
     * @return 返回诸如“上升25.0%”形式的字符串
     */
    private static String compare(int cur, int base) {
        if (cur == base) {
            return "无变化";
        } else if (cur > base) {
            return "上升" + DEC_FORMAT.format((cur * 1.0 / base - 1.0) * 100) + "%";
        } else {
            return "下降" + DEC_FORMAT.format((1.0 - cur * 1.0 / base) * 100) + "%";
        }
    }
}
